package com.studentachievement.dao;

import com.studentachievement.beans.Admin;
import com.studentachievement.beans.Score;
import com.studentachievement.beans.Student;
import com.studentachievement.beans.Course;
import com.studentachievement.utils.DatabaseUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class AdminDAO {
//    static public int countSearchOK = 0;
//    static public boolean searchIsOk = true;
    static{
        DatabaseUtil.MakeStudentprimaryKeyContiguous();
        DatabaseUtil.MakeCourseprimaryKeyContiguous();
        DatabaseUtil.MakeScoreprimaryKeyContiguous();
    }
    // 获取管理员信息
    public static Admin getAdminByUsernameAndPassword(String username, String password) {
        Admin admin = null;
        String sql = "SELECT * FROM admins WHERE username = ? AND password = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, username);
            ps.setString(2, password);

            rs = ps.executeQuery();
            if (rs.next()) {
                admin = new Admin();
                admin.setAdminId(rs.getInt("admin_id"));
                admin.setUsername(rs.getString("username"));
                admin.setPassword(rs.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close_(conn, ps, rs);
        }
        return admin;
    }
    public static List<Student> getAllStudents() {
        List<Student> students = new ArrayList<>();
        String sql = "SELECT student_id, name, gender, birth_date, major FROM students";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setStudentId(rs.getInt("student_id"));
                student.setName(rs.getString("name"));
                student.setGender(rs.getString("gender"));
                student.setBirthDate(String.valueOf(rs.getDate("birth_date")));
                student.setMajor(rs.getString("major"));
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close_(conn, ps, rs);
        }
        return students;
    }

    public static List<Course> getAllCourses() {
        List<Course> courses = new ArrayList<>();
        String sql = "SELECT course_id, course_name, teacher_name, credits FROM courses";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Course course = new Course();
                course.setCourseId(rs.getInt("course_id"));
                course.setCourseName(rs.getString("course_name"));
                course.setTeacherName(rs.getString("teacher_name"));
                course.setCredits(rs.getInt("credits"));
                courses.add(course);
            }

//            // 打印课程数据
//            System.out.println("课程数据：");
//            for (Course c : courses) {
//                System.out.println(c.getCourseName());
//            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close_(conn, ps, rs);
        }
        return courses;
    }


    public static List<Score> getAllScores() {
        List<Score> scores = new ArrayList<>();
        String sql = "SELECT s.score, s.exam_date, st.name AS student_name, c.course_name " +
                "FROM scores s " +
                "JOIN students st ON s.student_id = st.student_id " +
                "JOIN courses c ON s.course_id = c.course_id";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Score score = new Score();
                score.setScore(rs.getDouble("score"));
                score.setExamDate(String.valueOf(rs.getDate("exam_date")));
                score.setStudentName(rs.getString("student_name"));
                score.setCourseName(rs.getString("course_name"));
                scores.add(score);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close_(conn, ps, rs);
        }
        return scores;
    }

    /*==================================学生相关操作=============================*/
    // 删除学生
    public static boolean deleteStudent(int studentId) {
        String sql = "DELETE FROM students WHERE student_id = ? ";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, studentId);
            return ps.executeUpdate() != 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // 添加学生
    public static void addStudent(Student student) {
        String sql = "INSERT INTO students (name, gender, birth_date, major, password) VALUES (?, ?, ?, ?, ?)";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, student.getName());
            ps.setString(2, student.getGender());
            ps.setDate(3, Date.valueOf(student.getBirthDate()));
            ps.setString(4, student.getMajor());
            ps.setString(5, student.getPassword());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 修改学生信息
    public static void updateStudent( Student newInfo) {
        String sql = "UPDATE students SET name = ?, gender = ?, birth_date = ?, major = ?, password = ? WHERE student_id = ? ";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, newInfo.getName());
            ps.setString(2, newInfo.getGender());
            ps.setDate(3, Date.valueOf(newInfo.getBirthDate()));
            ps.setString(4, newInfo.getMajor());
            ps.setString(5, newInfo.getPassword());
            ps.setInt(6, newInfo.getStudentId());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 根据名字和学号查找学生信息
    public static Student getStudentById(int studentId) {
        Student student = null;
        String sql = "SELECT * FROM students WHERE student_id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, studentId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                student = new Student();
                student.setStudentId(rs.getInt("student_id"));
                student.setName(rs.getString("name"));
                student.setGender(rs.getString("gender"));
                student.setBirthDate(rs.getDate("birth_date").toString());
                student.setMajor(rs.getString("major"));
                student.setPassword(rs.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return student;
    }
    /*=====================================课程相关操作============================================*/
    // 删除课程
    public static boolean deleteCourse( int courseId) {
        String sql = "DELETE FROM courses WHERE course_id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, courseId);
            return ps.executeUpdate() != 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // 添加课程
    public static void addCourse(Course course) {
        String sql = "INSERT INTO courses (course_name, teacher_name, credits) VALUES (?, ?, ?)";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, course.getCourseName());
            ps.setString(2, course.getTeacherName());
            ps.setInt(3, course.getCredits());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 修改课程信息
    public static void updateCourse(Course newInfo) {
        String sql = "UPDATE courses SET course_name = ?, teacher_name = ?, credits = ? WHERE course_id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, newInfo.getCourseName());
            ps.setString(2, newInfo.getTeacherName());
            ps.setInt(3, newInfo.getCredits());
            ps.setInt(4, newInfo.getCourseId());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 根据名字和课程号查找课程信息
    public static Course getCourseById(int courseId) {
        Course course = null;
        String sql = "SELECT * FROM courses WHERE course_id = ? ";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, courseId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                course = new Course();
                course.setCourseId(rs.getInt("course_id"));
                course.setCourseName(rs.getString("course_name"));
                course.setTeacherName(rs.getString("teacher_name"));
                course.setCredits(rs.getInt("credits"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return course;
    }
    /*===========================================分数相关操作===============================================*/
    // 删除分数
    public static boolean deleteScore(int studentId, int courseId) {
        String sql = "DELETE FROM scores WHERE student_id = ? AND course_id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, studentId);
            ps.setInt(2, courseId);
            return ps.executeUpdate() != 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // 修改分数
    public static void updateScore(Score newScore) {
        String sql = "UPDATE scores SET score = ?, exam_date = ? WHERE student_id = ? AND course_id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setDouble(1, newScore.getScore());
            ps.setDate(2, Date.valueOf(newScore.getExamDate()));
            ps.setInt(3, newScore.getStudentId());
            ps.setInt(4, newScore.getCourseId());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 添加分数
    public static void addScore(Score score) {
        String sql = "INSERT INTO scores (student_id, course_id, score, exam_date) VALUES (?, ?, ?, ?)";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, score.getStudentId());
            ps.setInt(2, score.getCourseId());
            ps.setDouble(3, score.getScore());
            ps.setDate(4, Date.valueOf(score.getExamDate()));
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 根据学生号和课程号查找分数
    public static Score getScoreByStudentIdAndCourseId(int studentId, int courseId) {
        Score score = null;
        String sql = "SELECT * FROM scores WHERE student_id = ? AND course_id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, studentId);
            ps.setInt(2, courseId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                score = new Score();
                int student_id =rs.getInt("student_id");
                int course_id =rs.getInt("course_id");
                score.setStudentId(student_id);
                score.setCourseId(course_id);
                score.setScore(rs.getDouble("score"));
                score.setExamDate(rs.getDate("exam_date").toString());
                score.setStudentName(getStudentById(studentId).getName());
                score.setCourseName(getCourseById(course_id).getCourseName());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return score;
    }

}
